<?php
date_default_timezone_set('Europe/Paris');



class SQLBlog
{
	private $datasource;
	private $user;
	private $password;
	
	function __construct()
	{
		$this->user = 'root';
		$this->password = 'root';
		$this->datasource = 'mysql:host=localhost;dbname=blog;charset=utf8';
	}
 	
	
	//-----------------------------------------------------------------------------
	//						AFFICHAGE DE DONNEES
	//-----------------------------------------------------------------------------
	function AfficherPost()
	{
		try 
		{
			$base = new PDO ($this->datasource,$this->user,$this->password);
			
			$result = $base->query('SELECT P.*, CP.id_categorie FROM post P INNER JOIN categorie_post CP ON CP.id_post = P.id ORDER BY datePublication DESC');
			
			$rows_returned = $result->rowCount();
			
			while($row = $result->fetch(PDO::FETCH_ASSOC)) 
			{
				$array[] = $row;
			}
			$base=NULL;
			return ($rows_returned > 0 ? $array : false);
		}
		catch (PDOException $e)
		{
			var_dump($e->getTrace());
			die("Erreur !:".$e->getMessage().", ". $e->getTraceAsString());
			$base=NULL;
			return false;
		}
	}
	
	function AfficherPostParCategorie($id_categorie,$idUtilisateur)
	{
		try 
		{
			$base = new PDO ($this->datasource,$this->user,$this->password);
			
			$result = $base->query('SELECT P.*, CP.id_categorie FROM post P INNER JOIN categorie_post CP ON CP.id_post = P.id WHERE CP.id_categorie = '.$id_categorie.' ORDER BY datePublication DESC');
			
			$rows_returned = $result->rowCount();
			
			while($row = $result->fetch(PDO::FETCH_ASSOC)) 
			{
				$array[] = $row;
			}
			$base=NULL;
			return ($rows_returned > 0 ? $array : false);
		}
		catch (PDOException $e)
		{
			var_dump($e->getTrace());
			die("Erreur !:".$e->getMessage().", ". $e->getTraceAsString());
			$base=NULL;
			return false;
		}
	}
	
	function AfficherUnPost($idPost)
	{
		try 
		{
			$base = new PDO ($this->datasource,$this->user,$this->password);
			
			$result = $base->query('SELECT P.*,C.id_categorie FROM post P INNER JOIN categorie_post C ON C.id_post = P.id WHERE P.id='.$idPost);
			
			$rows_returned = $result->rowCount();
			
			while($row = $result->fetch(PDO::FETCH_ASSOC)) 
			{
				$array[] = $row;
			}
			$base=NULL;
			return ($rows_returned > 0 ? $array : false);
		}
		catch (PDOException $e)
		{
			var_dump($e->getTrace());
			die("Erreur !:".$e->getMessage().", ". $e->getTraceAsString());
			$base=NULL;
			return false;
		}
	}
	
	function AfficherCategories()
	{
		try 
		{
			$base = new PDO ($this->datasource,$this->user,$this->password);
			
			$result = $base->query('SELECT C.* FROM categorie C');
			
			$rows_returned = $result->rowCount();
			
			while($row = $result->fetch(PDO::FETCH_ASSOC)) 
			{
				$array[] = $row;
			}
			$base=NULL;
			return ($rows_returned > 0 ? $array : false);
		}
		catch (PDOException $e)
		{
			var_dump($e->getTrace());
			die("Erreur !:".$e->getMessage().", ". $e->getTraceAsString());
			$base=NULL;
			return false;
		}
	}
	
	
	
	function AfficherCommentairesParPost($idPost)
	{
		try 
		{
			$base = new PDO ($this->datasource,$this->user,$this->password);
			
			$result = $base->query('SELECT C.*, U.login, COALESCE(U.nom,\'supprimé)\') as nom , COALESCE(U.prenom,\'(utilisateur\') as prenom  FROM commentaire C LEFT JOIN UTILISATEUR U ON U.id = C.id_utilisateur WHERE C.id_post = '.$idPost.' ORDER BY dateCreation DESC');
			
			$rows_returned = $result->rowCount();
			
			while($row = $result->fetch(PDO::FETCH_ASSOC)) 
			{
				$array[] = $row;
			}
			$base=NULL;
			return ($rows_returned > 0 ? $array : false);
		}
		catch (PDOException $e)
		{
			var_dump($e->getTrace());
			die("Erreur !:".$e->getMessage().", ". $e->getTraceAsString());
			$base=NULL;
			return false;
		}
	}
	
	
	function AfficherUtilisateurs()
	{
		try 
		{
			$base = new PDO ($this->datasource,$this->user,$this->password);
			
			$result = $base->query('SELECT U.*, UD.id_droit FROM utilisateur U INNER JOIN utilisateur_droit UD ON UD.id_utilisateur = U.id ORDER BY dateInscription ASC');
			
			$rows_returned = $result->rowCount	();
			
			while($row = $result->fetch(PDO::FETCH_ASSOC)) 
			{
				$array[] = $row;
			}
			$base=NULL;
			return ($rows_returned > 0 ? $array : false);
		}
		catch (PDOException $e)
		{
			var_dump($e->getTrace());
			die("Erreur !:".$e->getMessage().", ". $e->getTraceAsString());
			$base=NULL;
			return false;
		}
	}
	
	function AfficherDroits()
	{
		try 
		{
			$base = new PDO ($this->datasource,$this->user,$this->password);
			
			$result = $base->query('SELECT D.* FROM droit D');
			
			$rows_returned = $result->rowCount();
			
			while($row = $result->fetch(PDO::FETCH_ASSOC)) 
			{
				$array[] = $row;
			}
			$base=NULL;
			return ($rows_returned > 0 ? $array : false);
		}
		catch (PDOException $e)
		{
			var_dump($e->getTrace());
			die("Erreur !:".$e->getMessage().", ". $e->getTraceAsString());
			$base=NULL;
			return false;
		}
	}
	
	function AfficherDonneesUtilisateurs($idUtilisateur)
	{
		try 
		{
			$base = new PDO ($this->datasource,$this->user,$this->password);
			
			$result = $base->query('SELECT * FROM utilisateur WHERE id='.$idUtilisateur);
			
			$rows_returned = $result->rowCount();
			
			while($row = $result->fetch(PDO::FETCH_ASSOC)) 
			{
				$array[] = $row;
			}
			$base=NULL;
			return ($rows_returned > 0 ? $array : false);
		}
		catch (PDOException $e)
		{
			var_dump($e->getTrace());
			die("Erreur !:".$e->getMessage().", ". $e->getTraceAsString());
			$base=NULL;
			return false;
		}
	}
	
	function VerificationUtilisateurExistant($texte)
	{
		try 
		{
			$base = new PDO ($this->datasource,$this->user,$this->password);
			
			$result = $base->query('SELECT * FROM utilisateur WHERE email=\''.$texte.'\'');
			
			$rows_returned = $result->rowCount();
			
			$base=NULL;
			
			return ($rows_returned);
		}
		catch (PDOException $e)
		{
			var_dump($e->getTrace());
			die("Erreur !:".$e->getMessage().", ". $e->getTraceAsString());
			$base=NULL;
			return false;
		}
	}
	
	function VerificationUtilisateurPourConnexion($email, $motDePasse)
	{
		try 
		{
			$base = new PDO ($this->datasource,$this->user,$this->password);
			
			$result = $base->query('SELECT * FROM utilisateur WHERE email=\''.$email.'\' AND motDePasse=\''.$motDePasse.'\'');
			
			$rows_returned = $result->rowCount();
			while($row = $result->fetch(PDO::FETCH_ASSOC)) 
			{
				$array[] = $row;
			}
			$base=NULL;
			
			return ($rows_returned > 0 ? $array : false);
		}
		catch (PDOException $e)
		{
			var_dump($e->getTrace());
			die("Erreur !:".$e->getMessage().", ". $e->getTraceAsString());
			$base=NULL;
			return false;
		}
	}
	
	function AfficherNombreUtilisateursConnectes()
	{
		try 
		{
			$base = new PDO ($this->datasource,$this->user,$this->password);
			
			$result = $base->query('SELECT * FROM utilisateur WHERE actif = 1');
			
			$rows_returned = $result->rowCount();

			$base=NULL;
			
			return ($rows_returned > 0 ? $rows_returned : false);
		}
		catch (PDOException $e)
		{
			var_dump($e->getTrace());
			die("Erreur !:".$e->getMessage().", ". $e->getTraceAsString());
			$base=NULL;
			return false;
		}
	}
	
	
	//-----------------------------------------------------------------------------
	
	
	
	//-----------------------------------------------------------------------------
	//						AJOUT DE DONNEES
	//-----------------------------------------------------------------------------
	
	function AjouterUtilisateur($email, $motDePasse, $nom, $prenom, $dateDeNaissance)
	{
		try 
		{
			$base = new PDO ($this->datasource,$this->user,$this->password);
			
			$result = $base->prepare('INSERT INTO utilisateur(login, motDePasse, nom, prenom, email, dateDeNaissance, dateInscription) 
													VALUES (?,?,?,?,?,?,?)');
			$execOk = $result->execute(array($email,$motDePasse,$nom,$prenom,$email,$dateDeNaissance,date("Y-m-d H:i:s")));
			
			if($execOk)
			{
				$idUtilisateurCree = $base->lastInsertId();
				
				$result = $base->query('INSERT INTO utilisateur_droit(id_droit,id_utilisateur)  VALUES (3,'.$idUtilisateurCree.')');
			
				if($result)
					return true;
			}
			
			return false;
			
			
			
		}
		catch (PDOException $e)
		{
			var_dump($e->getTrace());
			die("Erreur !:".$e->getMessage().", ". $e->getTraceAsString());
			$base=NULL;
			return false;
		}
	}
	
	function AjouterCommentairePourPost($commentaire, $idUtilisateur, $idPost)
	{
		try 
		{
			$base = new PDO ($this->datasource,$this->user,$this->password);
			
			$result = $base->prepare('INSERT INTO commentaire(texte,dateCreation,autorise,id_utilisateur,id_post) VALUES (?,?,?,?,?)');
			$execOk = $result->execute(array($commentaire,date("Y-m-d H:i:s"),1,$idUtilisateur,$idPost));
			
			return $execOk;
		}
		catch (PDOException $e)
		{
			var_dump($e->getTrace());
			die("Erreur !:".$e->getMessage().", ". $e->getTraceAsString());
			$base=NULL;
			return false;
		}
	}
	
	function AjouterPostPourCategorie($titre, $texte, $datePublication, $piecesJointes, $actif, $commentaireAutorise,$idCategorie,$idUtilisateur)
	{
		try 
		{
			$base = new PDO ($this->datasource,$this->user,$this->password);
			
			$result = $base->prepare('INSERT INTO post(titre,texte,dateCreation,datePublication,piecesJointes,actif,commentaireAutorise,id_utilisateur) 
													VALUES (?,?,?,?,?,?,?,?)');
			$execOk = $result->execute(array($titre,$texte,date("Y-m-d H:i:s"),$datePublication,$piecesJointes,$actif,$commentaireAutorise,$idUtilisateur));
			if($execOk)
			{
				$idPostCree = $base->lastInsertId();			
				$result = $base->query('INSERT INTO categorie_post(id_post, id_categorie) VALUES ('.$idPostCree.','.$idCategorie.')');
				
				if($result)
				{
					return true;
				}
				//else echo 'merde';
			}
			//else	
				return $false;
		}
		catch (PDOException $e)
		{
			var_dump($e->getTrace());
			die("Erreur !:".$e->getMessage().", ". $e->getTraceAsString());
			$base=NULL;
			return false;
		}
	}
	
	function AjouterCategorie($titre,$idUtilisateur)
	{
		try 
		{
			$base = new PDO ($this->datasource,$this->user,$this->password);
			
			$result = $base->prepare('INSERT INTO categorie(nom) VALUES (?)');
			$execOk = $result->execute(array($titre));
			
			return $execOk;
		}
		catch (PDOException $e)
		{
			var_dump($e->getTrace());
			die("Erreur !:".$e->getMessage().", ". $e->getTraceAsString());
			$base=NULL;
			return false;
		}
	}
	//-----------------------------------------------------------------------------
	
	
	
	//-----------------------------------------------------------------------------
	//						MISE A JOUR DE DONNEES
	//-----------------------------------------------------------------------------
	function MAJPost($titre, $texte, $piecesJointes, $actif, $commentaireAutorise, $idCategorie, $idPost, $idUtilisateur)
	{
		try 
		{
			$base = new PDO($this->datasource,$this->user,$this->password);
			
			$result = $base->prepare('UPDATE post SET titre=?,texte=?,piecesJointes=?,actif=?,commentaireAutorise=? WHERE id='.$idPost);														
			
			$execOk = $result->execute(array($titre,$texte,$piecesJointes,$actif,$commentaireAutorise));
			
			if($execOk)
			{
				$result = $base->prepare('UPDATE categorie_post SET id_categorie=? WHERE id_post='.$idPost);
				$execOk = $result->execute(array($idCategorie));
			}		
			
			return $execOk;
		}
		catch (PDOException $e)
		{
			var_dump($e->getTrace());
			die("Erreur !:".$e->getMessage().", ". $e->getTraceAsString());
			$base=NULL;
			return false;
		}	
	}
	
	function MAJDonneesUtilisateur( $nom, $prenom, $email, $dateDeNaissance, $idUtilisateur)
	{
		try 
		{
			$base = new PDO($this->datasource,$this->user,$this->password);
			
			$result = $base->prepare('UPDATE utilisateur SET nom=?,prenom=?,email=?,dateDeNaissance=? WHERE id='.$idUtilisateur);
			$execOk = $result->execute(array($nom,$prenom,$email,$dateDeNaissance));
			
			return $execOk;
		}
		catch (PDOException $e)
		{
			var_dump($e->getTrace());
			die("Erreur !:".$e->getMessage().", ". $e->getTraceAsString());
			$base=NULL;
			return false;
		}	
	}
	
	function MAJMotDePasseUtilisateur( $motDePasse, $idUtilisateur)
	{
		try 
		{
			$base = new PDO($this->datasource,$this->user,$this->password);
			
			$result = $base->prepare('UPDATE utilisateur SET motDePasse=? WHERE id='.$idUtilisateur);
			$execOk = $result->execute(array($motDePasse));
			
			return $execOk;
		}
		catch (PDOException $e)
		{
			var_dump($e->getTrace());
			die("Erreur !:".$e->getMessage().", ". $e->getTraceAsString());
			$base=NULL;
			return false;
		}	
	}
	
	function MAJDroitUtilisateur($idUtilisateur,$idDroit)
	{
		try 
		{
			$base = new PDO($this->datasource,$this->user,$this->password);
			
			$result = $base->prepare('UPDATE utilisateur_droit SET id_droit = ? WHERE id_utilisateur='.$idUtilisateur);
			
			$execOk = $result->execute(array($idDroit));
			
			return $execOk;
		}
		catch (PDOException $e)
		{
			var_dump($e->getTrace());
			die("Erreur !:".$e->getMessage().", ". $e->getTraceAsString());
			$base=NULL;
			return false;
		}	
	}
	
	
	//-----------------------------------------------------------------------------
	
	
	//-----------------------------------------------------------------------------
	//						SUPPRESSIONS DE DONNEES
	//-----------------------------------------------------------------------------
	function SupprimerUnPost($idPost, $idCategorie, $idUtilisateur)
	{
		try 
		{
			$base = new PDO($this->datasource,$this->user,$this->password);
			
			$execOk = $base->prepare('DELETE FROM categorie_post WHERE id_post='.$idPost);
			
			$result = $execOk->execute();
			
			if($result)
			{
				$result = $base->prepare('DELETE FROM commentaire WHERE id_post='.$idPost);
				
				$result->execute();	
				
				if($result)
				{
					$result = $base->prepare('DELETE FROM post WHERE id='.$idPost);
					
					$result->execute();	
					
					if($result)
					{
						return true;
					}
				}
			}
			
			
			return false;
		}
		catch (PDOException $e)
		{
			var_dump($e->getTrace());
			die("Erreur !:".$e->getMessage().", ". $e->getTraceAsString());
			$base=NULL;
			return false;
		}	
	}
	
	function SupprimerUneCategorie($idCategorie, $idUtilisateur)
	{
		try 
		{
			$base = new PDO($this->datasource,$this->user,$this->password);
			
			$execOk = $base->prepare('DELETE FROM categorie WHERE id='.$idCategorie);
			
			$execOk->execute();	
			return $execOk;
		}
		catch (PDOException $e)
		{
			var_dump($e->getTrace());
			die("Erreur !:".$e->getMessage().", ". $e->getTraceAsString());
			$base=NULL;
			return false;
		}	
	}
	
	function SupprimerUnCommentaire($idCommentaire, $idUtilisateur)
	{
		try 
		{
			$base = new PDO($this->datasource,$this->user,$this->password);
			
			$execOk = $base->prepare('DELETE FROM commentaire WHERE id='.$idCommentaire);
			
			$execOk->execute();	
			return $execOk;
		}
		catch (PDOException $e)
		{
			var_dump($e->getTrace());
			die("Erreur !:".$e->getMessage().", ". $e->getTraceAsString());
			$base=NULL;
			return false;
		}	
	}
	
	function SupprimerUnUtilisateur($idUtilisateurASupprimer, $idUtilisateur)
	{
		try 
		{
			$base = new PDO($this->datasource,$this->user,$this->password);
			
			$execOk = $base->prepare('DELETE FROM utilisateur_droit WHERE id_utilisateur='.$idUtilisateurASupprimer);
			
			$result = $execOk->execute();	
			
			if($result)
			{
				$execOk = $base->prepare('DELETE FROM utilisateur WHERE id='.$idUtilisateurASupprimer);
			
				$result = $execOk->execute();	
			}
			return $execOk;
		}
		catch (PDOException $e)
		{
			var_dump($e->getTrace());
			die("Erreur !:".$e->getMessage().", ". $e->getTraceAsString());
			$base=NULL;
			return false;
		}	
	}
	
	//-----------------------------------------------------------------------------
}
/*
$test = new SQLBlog();
if($test->SupprimerUnPost(4,1,null))
{
	echo 'ok';
}
else
echo 'kO';
*/
?>
